#!/usr/bin/env python3
#coding=utf-8

import pymysql

def init():
    conn = pymysql.connect(
        host='127.0.0.1',
        user='root',
        password='123456',
        port=3306,
        database='db1'
    )

    # 创建游标，查询数据以元组形式返回
    # cursor = conn.cursor()

    # 创建游标，查询数据以字典形式返回
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    cursor.execute("truncate table test_load")
    return conn, cursor

def fetch(conn, cursor):
    sql = 'select * from t1'
    try:
        cursor.execute(sql)
        # result = cursor.fetchall()  # 返回所有数据
        # result = cursor.fetchone()  # 返回一行数据
        result = cursor.fetchmany(2)  # fetchmany(size) 获取查询结果集中指定数量的记录，size默认为1
        print(result)
    except Exception as e:
        conn.rollback()
        print(e)
    finally:
        cursor.close()
        conn.close()

def insert(conn, cursor, id):
    # 元组、列表形式传参
    sql = "insert into test_load VALUES(%d, '%s')"
    try:
        cursor.execute(sql, (id, "cshi"))
        conn.commit()
    except Exception as e:
        conn.rollback()
        print(e)
    finally:
        cursor.close()
        conn.close()

    # 字典形式传参
    # sql = 'insert into test_load(PERSON_ID,PERSON_NAME,AGE) VALUES(%(person_id)s, %(person_name)s, %(age)s)'
    # try:
    #     cursor.execute(sql, {"person_id": "999998", "person_name": "kenny", "age": 28})
    #     conn.commit()
    # except Exception as e:
    #     conn.rollback()
    #     print(e)
    # finally:
    #     cursor.close()
    #     conn.close()

def demo():
    conn, cursor = init()
    for i in range(0, 10):
        insert(conn, cursor, i)

if __name__ == '__main__':
    demo()